Data Tools Open Source Overview for Eclipse Database Developer

Before you read this paper, you should already be familiar with the features and plug-ins in the org.eclipse.wst.rdb (relational database) open source contribution. At a minimum, you should go through the tutorial that is posted on the Eclipse Web site. This document is intends to provide an overview for a database tool developer that describes how to use and extend the Data Tools open source contribution.

Topics covered:

Background

Features of Data Tools

Supporting new database types

1) Creating a new database vendor

2) Extending the catalog loader

3) Extension point for SQL parser

4) Contributing a new JDBC driver

5) Extension point for DDL code generation

Re-usable Connection Wizard Pages Overview

JDBC Connection Configuration Page

1) Add new database manager

2) Add new JDBC driver

3) Set initial values for connection properties

4) Restrict which database managers are displayed (available WTP M4)

Existing Connections Page

1) Filter list of available connections

2) Modify displayed connection properties

3) Specify which existing connection is initially selected

This is the first step in our open source offering and we do encourage public contribution to new features through the extension of SQL Scrapbook, Server Explorer and Browse Data.

Background

What are SQLModel and Database definition model?

SQLModel is a meta-model based on the SQL 99/2003 specification, which in turn defines all database elements for the industry standard. A Database definition model derives from SQLModel, which implements all detailed database model elements. For example: tables; columns; user defined data types; relationships and constraints; stored procedures and their detailed parameters.

The Database definition model is then used as the base to set up a database-specific vendor document (XMI file). This vendor document is used as the source to define details about the database. For example, it can specify whether a database supports stored procedures or views. In addition, all data type support is also recorded in the vendor document. See the section below called Creating a new database vendor for details on how to create a vendor document for a new database vendor.

Features of the Data Tools open source contribution

Server Explorer – You can use the features in this view to connect to or disconnect from a database server using a native JDBC driver. This view also displays multiple live connections to database servers, and virtual folders containing tables, views, aliases, constraints, relationships, indexes, stored procedures, and user-defined functions. In addition to providing read-only capability, this view also supports a context menu that allows you to launch the SQL Scrapbook (described below), and browse data in a selected relational table or view.

Plug-ins that implement the Server Explorer: org.eclipse.wst.rdb.server.ui

Eclipse WST dependencies:

org.eclipse.wst.rdb.connection.ui

org.eclipse.wst.rdb.models.sql

org.eclipse.wst.rdb.core

org.eclipse.wst.rdb.core.ui

org.eclipse.wst.rdb.models.dbdefinition

org.eclipse.wst.rdb.outputview

org.eclipse.wst.common.navigator.views

SQL Scrapbook – There are three main ways to launch the SQL Scrapbook:

1) Right-click a database node in the Server Explorer and select Open SQL Scrapbook.

2) Click the Open SQL Scrapbook icon from the Server Explorer toolbar.

3) Click File > New > Other from the main menu, then expand the Data node in the New wizard window and select SQL Scrapbook Page.

In addition, the SQL Scrapbook opens when you open a SQL query resource file (.sql or .sqlpage file) from the Navigator view.

Plug-ins that implement SQL Scrapbook: org.eclipse.wst.rdb.sqlscrapbook

Eclipse WST dependencies:

org.eclipse.wst.rdb.core

org.eclipse.wst.rdb.core.ui

org.eclipse.wst.rdb.models.sql

org.eclipse.wst.rdb.server.ui

Connection Wizard – A default JDBC connection wizard is contributed as part of the open source contribution. To open the wizard, right-click in a blank area of the Server Explorer view and select New Connection. The wizard mainly consists of text fields that prompt the user for a suitable JDBC driver, database, host, port number, JDBC driver class, class location, and connection URL. To obtain a valid connection using the wizard you must ensure that the JDBC driver class, class location, and connection URL are properly configured.

Plug-ins that implement the connection wizard: org.eclipse.wst.rdb.connection.ui

Eclipse WST dependencies:

org.eclipse.wst.rdb.models.sql

org.eclipse.wst.rdb.models.dbdefinition

org.eclipse.wst.rdb.core

Output View – An Eclipse-based view that displays the return result set of a SQL query.

Plug-ins that implement the Output View: org.eclipse.wst.rdb.outputview

Eclipse WST dependencies:

None

Additional plug-ins – In addition to the feature-specific plug-ins mentioned above, the following plug-ins are contributed as core or required plug-ins to support the open source features. Most of these plug-ins are also listed above as dependencies for the feature-specific plug-ins.

org.eclipse.wst.rdb.core	Core, base features support
org.eclipse.wst.rdb.core.ui	UI, supporting core features
org.eclipse.wst.rdb.dbdefinition.*	Database definition for specific vendor
org.eclipse.wst.rdb.models.sql	SQLModel - meta-model defined using SQL99/2003 specification.

Supporting new database types

Using the Derby open source database as our example plug-in, we will show you step by step how to extend the open source framework.

Creating a new database vendor

Plug-ins used:

org.eclipse.wst.rdb.models.sql (SQLModel)
org.eclipse.wst.rdb.models.dbdefinition (Database Definition Model)

Here are two typical scenarios for creating a new database vendor:

Scenario one: Altering the SQLModel definition

Typically, you would not need to alter the definition for SQLModel. However, if you do need to add new model definition to the SQLModel, then complete the following steps:

1. Change the SQLModel (Rose model) file to add your database element.

2. Use the Eclipse framework to generate a new EMF model.

3. Generate code to create a new Database Definition Model.

4. Continue with step one of Scenario two.

Scenario two: Adding a new database that is SQL99 compliant

An example of this scenario is adding the Derby open source database. This example is not realistic, because the Database Definition Model for Derby has already been provided as part of the open source contribution, but you can follow these same steps to add other database vendors.

1. Generate a vendor document for the Derby database. Use the attached Java program to create a new database vendor (XMI) document. The Java class uses the database definition model (org.eclipse.wst.rdb.models.dbdefinition ) as a base, then walks through and sets each single model element (for example: column; view; trigger; constraint; and sequence). See the code snippet below for details. When it is finished, it will create a XMI document as your database vendor documentation. You should modify the content of this Java class to match features that your target database supports. For example, you would probably want to set the schema support to a value of ‘false’, if you are working on a MySQL database.

Java Class file for generating Vendor Primitive document

2. Create a plug-in for the Derby database definition. You can use the org.eclipse.wst.rdb.dbdefinition.derby plug-in as an example. In the plugin.xml file, you will need to include two extension points, databaseDefinition and uri_mapping, as follows:

<?xml version="1.0" encoding="UTF-8"?>

<?eclipse version="3.0"?>

<plugin

id="org.eclipse.wst.rdb.dbdefinition.derby"

name="%pluginName"

version="1.0.0"

provider-name="Eclipse">

<extension

point="org.eclipse.wst.rdb.core.databaseDefinition">

<definition

version="10.0"

product="Derby">

</definition>

</extension>

<extension

point="org.eclipse.emf.ecore.uri_mapping">

<mapping

source="Derby_10.0.xmi"

target="runtime/vendors/Derby_10.0/Derby_10.0.xmi">

</mapping>

</extension>

</plugin>

Important notes:

· The version number and product name are required in the databaseDefinition extension point.

· The 2^nd extension point uri_mapping is dependant on the vendor document file, which is formed by concatenating the product name and version name with an underscore. For example, Derby_10.0.xmi. Therefore, you must save your vendor document in the runtime/vendors/Derby_10.0/Derby_10.0.xmi folder of the org.eclipse.wst.rdb.dbdefinition.derby plug-in.

Extending the catalog loader

The catalog loader provides native JDBC catalog loading for all databases in the open source contribution. This means that a fixed set of database elements, such as schemas, tables, views, and relationships are loaded by querying the JDBC metadata. You can enhance the default catalog loader to process additional model elements that your target database supports. A common approach for this is to execute a specific query on the database server to retrieve preparatory information from the database system catalog.

To develop a catalog loader plug-in for Derby (org.eclipse.wst.rdb.derby), you would implement a catalog provider plug-in with following three required plug-ins:

org.eclipse.wst.rdb.models.sql

org.eclipse.wst.rdb.models.dbdefinition

org.eclipse.wst.rdb.core

Extension points:

org.eclipse.wst.rdb.core.catalog	for catalog loading
org.eclipse.wst.rdb.server.ui.ServerExplorerInitializationProvider	Contribute a new JDBC Driver for Derby

Your plugin.xml file should look like this:

<?xml version="1.0" encoding="UTF-8"?>

<?eclipse version="3.0"?>

<plugin

id=" org.eclipse.wst.rdb.derby"

name="Cloudscape Plug-in"

version="1.0.0"

provider-name="Eclipse"

class="org.eclipse.wst.rdb.derby.DerbyPlugin">

</library>

</runtime>

</requires>

<extension

point="org.eclipse.wst.rdb.core.catalog">

<catalog

version="10.0"

product="Derby"

provider=" org.eclipse.wst.rdb.derby.catalog.DerbyCatalogProvider">

</catalog>

</extension>

<extension

point="org.eclipse.wst.rdb.server.ui.ServerExplorerInitializationProvider">

<server

user=""

loadingPath="Plugin_Directory/driver/derby.jar"

password=""

driverClassName="org.apache.derby.jdbc.EmbeddedDriver"

serverName="localhost">

<database

URL="jdbc:derby:Plugin_State_Location/sample"

version="10.0"

name="Derby Sample"

product="Derby">

</database>

</server>

</extension>

</plugin>

A user interface (UI) plug-in (org.eclipse.wst.rdb.derby.ui) is also required, which has following dependencies:

org.eclipse.wst.rdb.server.ui

org.eclipse.wst.rdb.connection.ui

org.eclipse.wst.rdb.derby

org.eclipse.wst.rdb.models.sql

Your plugin.xml file should look like this:

<?xml version="1.0" encoding="UTF-8"?>

<?eclipse version="3.0"?>

<plugin

id="org.eclipse.wst.rdb.derby.ui"

name="Derby Plug-in"

version="1.0.0"

provider-name="Eclipse"

class=" org.eclipse.wst.rdb.derby.ui.DerbyUIPlugin">

</library>

</runtime>

</requires>

<databaseDefinition

product="Derby"

version="10.0">

</databaseDefinition>

<driverContribution

driverName="Derby Embedded JDBC Driver"

uiContributor="org.eclipse.wst.rdb.derby.internal.ui.connection.dialogs.DerbyNewConnectionProvider">

</driverContribution>

</extension>

</plugin>

Extension point:

org.eclipse.wst.rdb.connection.ui.connectionUIProvider

For Connection Wizard UI – JDBC Driver info for catalog loading

Finally, you can follow the JDBC provider implementation in the org.eclipse.wst.rdb.core plug-in (in the \src\org\eclipse\wst\rdb\internal\core\rte\jdbc package, all JDBC* classes such as JDBCDatabase, JDBCTable, JDBCView, and JDBCSchema) to implement the catalog loader for Derby. Or refer to the classes in org.eclipse.wst.rdb.derby plug-in, catalog package.

Extension point for the SQL parser

The extension point to implement a SQL Parser for reverse engineering or catalog loading from the DDL Script is defined in the org.eclipse.wst.rdb.core plug-in, in the schema folder ddlParser.exsd.

org.eclipse.wst.rdb.core.ddlParser

Sample plugin.xml:

……

<extension

point="org.eclipse.wst.rdb.core.ddlParser">

<parser

version="10.0"

product="Derby"

<!—You need to add this class to Derby plug-in -->

class="org.eclipse.wst.rdb.derby.ddl.DerbyDdlEngineeringProvider">

</parser>

</extension>

…….

The DerbyDdlEngineeringProvider class implements the following interface.

public interface DDLParser {

public Database[] parse(String fileName, IProgressMonitor progressMonitor);

}

Contributing a new JDBC driver

This was touched on in the sample Derby plug-in. See this section of the document for implementation details.

Extension point for DDL Code generation

The extension point to implement DDL Code Generation (for forward engineering) is defined in the org.eclipse.wst.rdb.core plug-in, in the schema folder ddlGeneration.exsd.

org.eclipse.wst.rdb.core.DDLGenerator

Sample plugin.xml:

……

<extension

point="org.eclipse.wst.rdb.core.ddlGeneration">

<generator

product="Derby"

version="10.0"

<!—You need to add this to Derby plug-in -->

class="org.eclipse.wst.rdb.derby.ddl.DerbyDdlGenerator">

</generator>

</extension>

…….

The DerbyDdlGenerator class implements the following interface.

public interface DDLGenerator {

public String[] forwardEngineer(SQLObject[] elements, IProgressMonitor progressMonitor);

public EngineeringOption[] getOptions();

}

Re-usable Connection Wizard Pages

In a software development tool, it is common for tools that access databases to require the user to specify a database connection during a wizard-driven development task. The user can typically choose from a list of previously configured connections or specify the properties for a new connection. The RDB component provides two wizard pages that wizard developers can incorporate into their wizards to accomplish this: the existing connections wizard page (ExistingConnectionsWizardPage.java) and the JDBC configuration wizard page (NewCWJDBCPage.java).

Features:

JDBC Connection Configuration Page

1. Add new database manager

2. Add new JDBC driver

3. Set initial values for connection properties

4. Restrict which database managers are displayed (available WTP M4)

Existing Connections Page

1. Filter list of available connections

2. Modify displayed connection properties

3. Specify which existing connection is initially selected

Sample code for the re-usable connection wizard pages is available here.

Add New Database Manager

The list of database managers is generated by enumerating all of the available database definitions. For example, the org.eclipse.wst.rdb.dbdefinition.sybase plug-in contains the database definition for Sybase 12.x.

The data definition defines the data types supported by the database manager as well as other database properties such as whether triggers and identity columns are supported. New database definitions can be added to expand the list of available database managers. The details of implementing a database definition were covered earlier in this article.

Add New JDBC Driver

For each database manager, new drivers can be added which will appear in the JDBC driver combo box. Adding new driver involves two steps:

1. Implement a class which implements the IServerConnectionUIProvider interface.

2. Register the class with the org.eclipse.wst.rdb.connection.ui. -connectionUIProvider extension point.

<databaseDefinition

product="*"

version="*">

</databaseDefinition>

<driverContribution

driverName="Other"

uiContributor="org.eclipse.wst.rdb.connection.internal.ui.wizards.other.OtherNewConnectionProvider">

</driverContribution>

</extension>

Note: The product and version values can be specific products and versions. These product and version values should match a product and version defined in a database definition.

Set Initial Values for Connection Properties

The initial values for the properties in the JDBC configuration can be set programmatically by calling the setConnectionProperties() method.

Example:

ConnectionWizardProperties wizProps = new ConnectionWizardProperties();

wizProps.setConnectionName("SAMPLE");

wizProps.setProduct("DB2 UDB");

wizProps.setVersion("8.2");

wizProps.setDriverName("IBM DB2 Universal");

wizProps.setClassLocation("C:\\Program…cisuz.jar");

wizProps.setURL("jdbc:db2://localhost:50000/SAMPLE");

wizProps.setUserID("db2admin");

jdbcPage.setConnectionProperties(wizProps);

Restrict Which Database Managers Are Displayed (available WTP M4)

A wizard may want to restrict the list of database managers available to the user. The setAllowedProductVersions() method allows the wizard to indicate which database managers should be displayed.

Example:

jdbcPage.setAllowedProductVersions(

new DatabaseProductVersion[] {

new DatabaseProductVersion("Sybase", "12.x")

});

Filter List of Available Connections

In the existing connection wizard page, the list of available connections may need to be restricted, for example, to showing only the connection for a specific database manager. This can be accomplished by extending the existing connections wizard page and overriding the getConnectionsToDisplay() method.

Example:

protected ConnectionInfo[] getConnectionsToDisplay() {

String vendor = "DB2 UDB";

String version = "V8.2";

Vector filteredInfosCollection = new Vector();

ConnectionInfo[] filteredInfos = new ConnectionInfo[] {};

ConnectionInfo[] allInfos = RDBCorePlugin.getDefault()

.getConnectionManager().getAllNamedConnectionInfo();

final int infoLength = allInfos.length;

if (infoLength > 0) {

for (int index = 0; index < infoLength; index++) {

System.out.println(allInfos[index].getDatabaseDefinition()

.getProduct()

+ ":"

+ allInfos[index].getDatabaseDefinition().getVersion());

if (allInfos[index].getDatabaseDefinition().getProduct()

.equals(vendor)

&& allInfos[index].getDatabaseDefinition().getVersion()

.equals(version)) {

filteredInfosCollection.add(allInfos[index]);

}

filteredInfos = new ConnectionInfo[filteredInfosCollection

.size()];

filteredInfosCollection.copyInto(filteredInfos);

}

return filteredInfos;

}

Modify Displayed Connection Properties

The properties displayed for each connection in the existing connections wizard page can be modified by extending the wizard page and overriding the updateConnectionDisplayProperties() method. The default properties can be removed and/or new properties appended.

Example:

protected ConnectionDisplayProperty[] updateConnectionDisplayProperties(

ConnectionInfo connectionInfo,

ConnectionDisplayProperty[] defaultDisplayProperties) {

ConnectionDisplayProperty[] properties = null;

Vector propertiesCollection = new Vector();

propertiesCollection.addAll(Arrays.asList(defaultDisplayProperties));

propertiesCollection

.add(new ConnectionDisplayProperty("My Test Property",

connectionInfo.getDatabaseProductVersion()));

properties = new ConnectionDisplayProperty[propertiesCollection.size()];

propertiesCollection.toArray(properties);

return properties;

}

Specify Which Existing Connection Is Initially Selected

The initially selected existing connection can be set by calling setDefaultConnection(). The specified connection will appear selected in the existing connections wizard page.

Example:

myExistingConnectionsPage.setDefaultConnection("SAMPLE");